Custom Functions

Function Call

Expected

Actual

OneWeekLater( date(2022,1,1) )

2022-01-08

 

OneWeekLater( date(2022,12,25) )

2023-01-01

 

OxfordComma( list() )

 

 

OxfordComma( list(“a”) )

a

 

OxfordComma( list(“a”,”b”) )

a and b

 

OxfordComma( list(“a”,”b”,”c”,”d” ) )

a, b, c, and d

 

MyTermInclusive( date(2021,12,31), date(2023,3,24) )

One Year,Two Months,Three Weeks,Four Days

 

MyTermExclusive( date(2021,12,31), date(2023,3,24) )

One Year,Two Months,Three Weeks,Three Days

 

e()

2.71828182845905

 

ln2()

0.69314718055995

 

ln10()

2.30258509299405

 

log2e()

1.44269504088896

 

log10e()

0.43429448190325

 

pi()

3.14159265358979

 

sqrt1_2()

0.70710678118655

 

sqrt2()

1.4142135623731

 

abs( -0.123 )

0.123

 

cbrt( -1 )

-1

 

imul(3,4)

12

 

max(-3,1)

1

 

min(-3,1)

-3

 

pow(7,-2)

0.02040816326531

 

random()

 

 

sign(-0.123)

-1

 

sqrt(9)

3

 

ceil(-1.001)

-1

 

floor(-1.001)

-2

 

fround(-5.05)

-5.05000019073486

 

round(-5.05)

-5.1

 

trunc(-5.05)

-5

 

DegreesToRadians(180)

3.14159265358979

 

RadiansToDegrees(3.14159265358979)

179.99999999999983

 

acos(1)

0

 

acosh(1)

0

 

asin(1)

1.5707963267949

 

asinh(1)

0.88137358701954

 

atan(1)

0.78539816339745

 

atanh(1)

...

 

cos(1)

0.54030230586814

 

cosh(1)

1.54308063481524

 

sin(1)

0.8414709848079

 

sinh(1)

1.1752011936438

 

tan(1)

1.5574077246549

 

tanh(1)

0.76159415595576

 

hypot(3,4,5)

7.07106781186548

 

fv( 0.05, 120, 100, 10000, true )

-4219735.02657316066325

 

pv( 0.05, 120, 100, -4219735, true )

9999.99992383993049

 

nper( 0.05, 100, 10000, -4219735, true )

119.99999987099406

 

rate( 120, 100, 10000, -4219735, true )

0.00000596046448

 

pmt( 0.05, 120, 10000, -4219735, true )

99.9999963629059

 

ipmt( 0.05, 59, 120, 10000, -4219735, true )

-9662.14870862321186

 

ppmt( 0.05, 59, 120, 10000, -4219735, true )

9762.14870498611708

 

sln( 100, 10, 2 )

45

 

syd( 100, 10, 2, 1 )

60

 

db( 100, 10, 2, 1, 6 )

34.1886116991581

 

ddb( 100, 10, 2, 2, 1 )

50

 

vdb( 100, 10, 2, 1, 2, 1 )

50

 

SYNTAX()

…

 

NORETURN()

…

 

ERROR()

…

 

LOOPYFOR()

…

 

LOOPYWHILE()

…

 

LOOPYDO()

…

 

 

FORMATS

Name

Separator 1

Separator 2

Separator 3

Example

Oxford Comma

#, #

# and #

#, and #

a, b, c, and d

 

Name

Years

Months

Weeks

Days

Example

TermAll

true

true

true

true

1 Year,2 Months,3 Weeks,4 Days

 

 

Custom FunctionS

Lambda Functions

The definition of each function is a computation.

Name

Note

Parameters

Computation

OneWeekLater

One week after the specified date

date

AddDays( date, 7 )

OxfordComma

Apply the Oxford Comma format to a list of texts

list

Format( list, "Oxford Comma" )

MyTermInclusive

The term between two dates (inclusive) using cardinal numbers

start , finish

Term( start, finish, "Term All + cardinal" )

MyTermExclusive

The term between two dates (exclusive) using cardinal numbers

start , finish

MyTermInclusive( start, AddDays( finish, -1 ) )

 

Javascript Functions

The definition of each function is a series of Javascript statements finishing with a return statement.

Math

Name

Note

Parameters

Javascript

E

 

 

return Math.E ;

LN2

 

 

return Math.LN2 ;

LN10

 

 

return Math.LN10 ;

LOG2E

 

 

return Math.LOG2E ;

LOG10E

 

 

return Math.LOG10E ;

PI

 

 

return Math.PI ;

SQRT1_2

 

 

return Math.SQRT1_2 ;

SQRT2

 

 

return Math.SQRT2 ;

abs

 

a

return Math.abs( a ) ;

cbrt

 

a

return Math.cbrt( a ) ;

imul

 

a,b

return Math.imul( a, b ) ;

max

 

a,b

return Math.max( a, b ) ;

min

 

a,b

return Math.min( a, b ) ;

pow

 

a,b

return Math.pow( a, b ) ;

random

 

 

return Math.random() ;

sign

 

a

return Math.sign( a ) ;

sqrt

 

a

return Math.sqrt( a ) ;

ceil

 

a

return Math.ceil( a ) ;

floor

 

a

return Math.floor( a ) ;

fround

 

a

return Math.fround( a ) ;

round

 

a

return Math.round( a ) ;

trunc

 

a

return Math.trunc( a ) ;

DegreesToRadians

 

a

return a * ( Math.PI / 180 ) ;

RadiansToDegrees

 

a

return a / ( Math.PI / 180 ) ;

acos

 

a

return Math.acos( a ) ;

acosh

 

a

return Math.acosh( a ) ;

asin

 

a

return Math.asin( a ) ;

asinh

 

a

return Math.asinh( a ) ;

atan

 

a

return Math.atan( a ) ;

atanh

 

a

return Math.atanh( a ) ;

cos

 

a

return Math.cos( a ) ;

cosh

 

a

return Math.cosh( a ) ;

sin

 

a

return Math.sin( a ) ;

sinh

 

a

return Math.sinh( a ) ;

tan

 

a

return Math.tan( a ) ;

tanh

 

a

return Math.tanh( a ) ;

hypot

 

a,b,c

return Math.hypot( a, b, c ) ;

 

Microsoft Excel® Investment

Find solutions to the equations:

     r > 0 :    pv.(1 + r)n + c.(1 + r.b).((1 + r) n - 1)/r + fv = 0

     r = 0 :    pv + c.n + fv = 0

where:

               pv          the present value of a periodic investment

               fv          the future value of a periodic investment

               n            the number of periods (life) in a periodic investment

               c            the investment amount per period

               r            the interest rate per period

               b            true       each investment is made at the beginning of each period, or
                              false      each investment is made at the end of each period

Name

Note

Parameters

Javascript

fv

the future value of an investment

r, n, c, pv, b

var rn = Math.pow( ( 1 + r ), n ) ;

var rb = ( b ? r : 0 ) ;

var fv = ( ( r < 0.000001 )

         ? - ( pv + ( c * n ) )

         : - ( ( pv * rn ) + ( c * ( 1 + rb ) * ( rn - 1 ) / r ) ) ) ;

 

return fv ;

pv

the present value that is required for an investment to reach a future value

r, n, c, fv, b

var rn = Math.pow( ( 1 + r ), n ) ;

var rb = ( b ? r : 0 ) ;

var pv = ( ( r < 0.000001 )

         ? - ( fv + ( c * n ) )

         : - ( ( c * ( 1 + rb ) * ( rn - 1 ) / r ) + fv ) / rn ) ;

 

return pv ;

nper

the number of periods required for an investment to grow from a present value to a future value

r, c, pv, fv, b

var rb = ( b ? r : 0 ) ;

var n  = ( ( r < 0.000001 )

         ? - (fv + pv) / c

         : Math.log( ( c * ( 1 + rb ) - ( fv * r ) ) / ( c * ( 1 + rb ) + ( pv * r ) ) ) / Math.log( 1 + r ) ) ;

 

return n ;

rate

the interest rate required for an investment to grow from a present value to a future value

n, c, pv, fv, b

var rmin = 0.00 ;

var rmax = 100 ;

var r  ;

var rn ;

var rb ;

var rf ;

 

do

{

  r    = rmax ;

  rn   = Math.pow( ( 1 + r ), n ) ;

  rb   = ( b ? r : 0 )            ;

  rf   = ( ( pv * rn ) + ( c * ( 1 + rb ) * ( rn - 1 ) / r ) + fv ) ;

  rmax = rmax / 2 ;

}

while ( rf >= 0 )

 

do

{

  r  = ( rmin + rmax ) / 2 ;

  rn = Math.pow( ( 1 + r ), n ) ;

  rb = ( b ? r : 0 )            ;

  rf = ( ( pv * rn ) + ( c * ( 1 + rb ) * ( rn - 1 ) / r ) + fv ) ;

  if ( rf < 0 )

  {

    rmax = r ;

  }

  else

  {

    rmin = r ;

  }

}

while ( Math.abs( rf ) > 0.00001 && ( rmax - rmin ) > 0.00001 )

 

return r ;

pmt

the periodic payment required for an investment to grow from a present value to a future value

r, n, pv, fv, b

var rn = Math.pow( ( 1 + r ), n ) ;

var rb = ( b ? r : 0 ) ;

var c  = ( ( r < 0.000001 )                                               

         ? - ( fv + pv ) / n

         : - ( ( fv + ( pv * rn ) ) * r / ( ( 1 + rb ) * ( rn - 1 ) ) ) ) ;

 

return c ;

ipmt

the interest for a specific period in an investment series

r, e, n, pv, fv, b

if ( e <= 1 )

{

  return 0 ;

}

 

var rn = Math.pow( ( 1 + r ), n ) ;

var rb = ( b ? r : 0 ) ;

var c  = ( ( r < 0.000001 )                                               

         ? - ( fv + pv ) / n

         : - ( ( fv + ( pv * rn ) ) * r / ( ( 1 + rb ) * ( rn - 1 ) ) ) ) ;

var i  = c

       - ( c * Math.pow( ( 1 + r ), ( e - 1 ) ) )

       - ( pv * r * Math.pow( ( 1 + r ), ( e - 1 - ( b ? 1 : 0 ) ) ) ) ;

 

return i ;

ppmt

the net periodic payment required for an investment to grow from a present value to a future value, less the interest for the specified period

r, e, n, pv, fv, b

if ( e <= 1 )

{

  return 0 ;

}

 

var rn = Math.pow( ( 1 + r ), n ) ;

var rb = ( b ? r : 0 ) ;

var c  = ( ( r < 0.000001 )                                               

         ? - ( fv + pv ) / n

         : - ( ( fv + ( pv * rn ) ) * r / ( ( 1 + rb ) * ( rn - 1 ) ) ) ) ;

var i  = c

       - ( c * Math.pow( ( 1 + r ), ( e - 1 ) ) )

       - ( pv * r * Math.pow( ( 1 + r ), ( e - 1 - ( b ? 1 : 0 ) ) ) ) ;

 

return ( c - i ) ;

 

Microsoft Excel® Depreciation

Name

Description

Parameters

Javascript

Sln

straight line

cost, salvage, life

return ( cost - salvage ) / life ;

Syd

sum of years

cost, salvage, life, period

return ( ( cost - salvage ) * ( life - period + 1 ) * 2 ) / ( life * ( life + 1 ) ) ;

Db

fixed declining balance

cost, salvage, life, period, months

var rate = ( 1 - Math.pow( ( salvage / cost ), ( 1 / life ) ) ) ;

 

// first year

var totaldepreciation = ( cost * rate * ( months / 12 ) ) ;

 

if ( period > 1 )

{

  // intermediate years

  for ( var j = 2 ; j < period ; j ++ )

  {

    totaldepreciation += ( ( cost - totaldepreciation ) * rate ) ;

  }

 

  // final year

  var finalyearproportion = ( ( months !== 12 && period === life + 1 )

                            ? ( ( 12 - months ) / 12 )

                            : 1 ) ;

  totaldepreciation = ( cost - totaldepreciation ) * rate * finalyearproportion ;

}

 

return totaldepreciation ;

Ddb

double declining balance

cost, salvage, life, period, factor

return this.vdb( cost, salvage, life, ( period - 1 ), period, factor ) ;

Vdb

variable double declining balance

cost, salvage, life, startperiod, finishperiod, factor

var startdepreciation = 0 ;

 

for ( var j = 0 ; j < startperiod ; j ++ )

{

  var periodDepreciation = Math.min(

                           ( ( cost - startdepreciation ) * ( factor / life ) ),

                           ( ( cost - startdepreciation ) - salvage           ) ) ;

 

  startdepreciation += periodDepreciation ;

}

 

var totaldepreciation = startdepreciation ;

 

for ( var j = startperiod ; j < finishperiod ; j ++ )

{

  var perioddepreciation = Math.min(

                           ( ( cost - totaldepreciation ) * ( factor / life ) ),

                           ( ( cost - totaldepreciation ) - salvage           ) ) ;

 

  totaldepreciation += periodDepreciation ;

}

 

return totaldepreciation - startdepreciation ;

 

Bad Definitions

Name

Description

Parameters

Javascript

SYNTAX

The code is not Javascript

 

variable a = 1 ;

variable b = 2 ;

returning a + b ;

NORETURN

The Javascript code does not return a value

 

var a = 1 ;

var b = 2 ;

ERROR

The Javascript code throws an error

 

return UnknownFunction() ;

LOOPYFOR

The Javascript code contains a FOR loop that exceeds the maximum number of iterations (1000)

 

for ( var i = 0 ; i <= 1001 ; i ++ )

{

  console.log( i ) ;

}              

 

return 1234 ;

LOOPYWHILE

The Javascript code contains an infinite WHILE loop

 

var j = 0 ;

while ( true )

{

  console.log( j ) ;

  j ++ ;

}

 

return 1234 ;

LOOPYDO

The Javascript code contains an infinite DO loop

 

var k = 0 ;

do

{

  console.log( k ) ;

  k ++ ;

}

while ( true )

 

return 1234 ;

 

 

Baked-In Skeleton Configuration

Row

Column

Cell

Height

HeightNoWording

Width

WidthNoWording

Style

Note

single

 

 

100%

 

 

 

 

The row containing the wording

 

single

 

 

 

100%

 

 

The column containing the wording

 

 

Wording

100%

 

 

 

 

The wording of the document to be assembled

 

Baked-In Behavior Configuration

Group

Setting

Value

Note

Wording

Visible

true

The wording is initially visible